SQLite with Entity Framework Code First

1. Prerequisites
2. Steps
3. Option

Entity Framework Code First默认使用SQL Server，这里提供使用SQLite的方法。

Prerequisites

支持NuGet和Entity Framework Code First的Visual Studio。

Steps

1. 新建工程，在Solution Explorer里右键工程，选Manage NuGet Packages。

2. 找到System.Data.SQLite并安装。

3. 参考Entity Framework Code First，新建一个Context（如UserContext），然后在.config（如Web.config）中加入连接字符串连接数据库mydb.db：

   <connectionStrings>
     <add name="UserContext" connectionString="Data Source=|DataDirectory|mydb.db" providerName="System.Data.SQLite.EF6" />
   </connectionStrings>

   同时修改（不加入System.Data.SQLite会出错，参考http://www.codeproject.com/Questions/731487/No-Entity-Framework-provider-found-for-the-ADO-NET）

   <DbProviderFactories>
     <remove invariant="System.Data.SQLite.EF6" />
     <add name="SQLite Data Provider" invariant="System.Data.SQLite.EF6" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
     <remove invariant="System.Data.SQLite.EF6" />
     <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
     <remove invariant="System.Data.SQLite" />
     <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
   </DbProviderFactories>

   这样理论上就能通过DbContext以及DbSet等Entity Framework Code First的操作了。

Option

• 新建SQLiteDbHelper在程序开始运行时自动新建数据库并建立相关的table。如以下示例：

  using System.Data.SQLite;
  
  namespace Test
  {
      public class SQLiteDbHelper
      {
          public const string TABLE_USERS = "Users";
  
          private SQLiteConnection connection;
  
          /// <summary>
          /// Init SQLite DB connectionString.
          /// </summary>
          /// <param name="dbFilePath">SQLite Database file path</param>
          public SQLiteDbHelper(string dbFilePath)
          {
              string connectionString = "Data Source=" + dbFilePath;
              connection = new SQLiteConnection(connectionString);
          }
  
          /// <summary>
          /// Create DB and tables.
          /// </summary>
          public void Create()
          {
              connection.Open();
              SQLiteCommand command = connection.CreateCommand();
              command.CommandText = string.Format("CREATE TABLE {0}({1}, {2}, {3}, {4})",
                  TABLE_USERS, "_id INTEGER PRIMARY KEY AUTOINCREMENT",
                  "Name VARCHAR", "Email VARCHAR", "Password VARCHAR");
              command.ExecuteNonQuery();
              connection.Close();
          }
      }
  
          /// <summary>
          /// Executes a Transact-SQL statement against the connection and returns the number
          /// of rows affected.
          /// </summary>
          /// <param name="sql">sql command</param>
          /// <param name="parameters">The parameters of sql command.</param>
          /// <returns>The number of rows affected.</returns>
          public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
          {
              int affectedRows = 0;
              connection.Open();
              using (DbTransaction transaction = connection.BeginTransaction())
              {
                  using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                  {
                      if (parameters != null)
                      {
                          command.Parameters.AddRange(parameters);
                      }
                      affectedRows = command.ExecuteNonQuery();
                  }
                  transaction.Commit();
              }
              connection.Close();
              return affectedRows;
          }
  
          /// <summary>
          /// Sends the command to the connection and builds a SQLiteDataReader.
          /// </summary>
          /// <param name="sql">sql command</param>
          /// <param name="parameters">The parameters of sql command.</param>
          /// <returns>A SQLiteDataReader object.</returns>
          public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
          {
              SQLiteCommand command = new SQLiteCommand(sql, connection);
              if (parameters != null)
              {
                  command.Parameters.AddRange(parameters);
              }
              connection.Open();
              return command.ExecuteReader(CommandBehavior.CloseConnection);
          }
  
          /// <summary>
          /// Executes the query, and returns the first column of the first row in the result
          /// set returned by the query. Additional columns or rows are ignored.
          /// </summary>
          /// <param name="sql">sql command</param>
          /// <param name="parameters">The parameters of sql command.</param>
          /// <returns>The first column of the first row in the result set, or a null reference
          /// if the result set is empty. Returns a maximum of 2033 characters.</returns>
          public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
          {
              using (SQLiteCommand command = new SQLiteCommand(sql, connection))
              {
                  if (parameters != null)
                  {
                      command.Parameters.AddRange(parameters);
                  }
                  SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                  DataTable dataTable = new DataTable();
                  adapter.Fill(dataTable);
                  return dataTable;
              }
          }
      }
  }

• 初始化DB

  // Init DB.
  string dbFilePath = Path.Combine(AppDomain.CurrentDomain.GetData("DataDirectory").ToString(), "mydb.db");
  SQLiteDbHelper sqliteDbHelper = new SQLiteDbHelper(dbFilePath);
  if (!File.Exists(dbFilePath))
  {
      sqliteDbHelper.Create();
  }